from datetime import datetime, date
import akshare as ak
import pandas as pd
import numpy as np
from sqlalchemy import create_engine, distinct, or_, and_
import pymssql
from urllib.parse import quote_plus as urlquote
from configparser import ConfigParser
import time

pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)

conf = ConfigParser()
conf.read('env.ini')

sqlserver = ('sqlserver_out', 'sqlserver_in')[0]

host = conf.get(sqlserver, 'host')
port = conf.get(sqlserver, 'port')
user = conf.get(sqlserver, 'user')
password = conf.get(sqlserver, 'password')
schema = conf.get(sqlserver, 'schema')
db_name = conf.get(sqlserver, 'db')


def insert_data():
    sql1 = "SELECT DISTINCT code FROM ETF_DAY_DATA_BAK"
    r1 = exec_sql(sql1)
    stocks = set([tok[0] for tok in r1])
    for i, code in enumerate(stocks):
        print(f'i={i}->len={len(stocks)}->code={code}')
        sql2 = f"SELECT code, datetime, [close], [open], low, high, volume, money FROM ETF_DAY_DATA_BAK WHERE code={code}"
        r2 = exec_sql(sql2)
        df = pd.DataFrame(r2, columns=['code', 'datetime', 'close', 'open', 'low', 'high', 'volume', 'money'])
        df['datetime'] = df['datetime'].map(time_map)
        insert_db(df, 'ETF_DAY_DATA')


def exec_sql(sql):
    conn = pymssql.connect(host=host, port=port, user=user, password=password, database=db_name)
    cursor = conn.cursor()
    cursor.execute(sql)
    r = cursor.fetchall()
    cursor.close()
    conn.close()
    return r


def time_map(x):
    # print(x)
    t = int(datetime.strftime(datetime.strptime(x, '%Y-%m-%d'), '%Y%m%d'))
    return t


def insert_db(df, table_name):
    yconnect = create_engine(f'mssql+pymssql://{user}:{urlquote(password)}@{host}:{port}/{db_name}?charset=utf8')
    pd.io.sql.to_sql(df, table_name, yconnect, schema=schema, if_exists='append', index=False)


def set_date():
    st = "20130101"
    et = "20230818"
    st_d = datetime.strptime(st, '%Y%m%d').date()
    et_d = datetime.strptime(et, '%Y%m%d').date()
    res = {'st': st, 'et': et, 'st_d': st_d, 'et_d': et_d}

    return res


def main():
    insert_data()


if __name__ == '__main__':
    main()
